


ALTER   function fn_get_lot_shipping_expected_date(@lot_id bigint,
						@date_in datetime,
						@next_process_step_id bigint,
						@next_location_id int,
						@is_express int)
returns @fn_results Table(Expected_Date datetime)

AS
begin
	-- declare @results Table(Expected_Date datetime)
	declare @temp_exp_date datetime
	declare @temp_cycle_time int
	declare @temp_ship_time datetime
	declare @temp_process_step_id bigint
	declare @temp_location_id int
	declare @temp_is_express bit
	declare @temp_tt_normail_time int
	
	select @temp_process_step_id = ProcessStep_ID, @temp_location_id = Location_ID from Lot_Table where Lot_ID = @lot_id
	select @temp_is_express = isExpress from Lot_Shipping_Instructions_Table where Lot_ID = @lot_id
	--print 'isExpress = '
	--print @temp_is_express
	if @temp_is_express is not null
	begin
		select @temp_cycle_time = PSLT_Cycle_Time_Express from Process_Step_Location_Table where ProcessStep_ID = @temp_process_step_id and Location_ID = @temp_location_id
	end
	else
	begin
		select @temp_cycle_time = PSLT_Cycle_Time_Normal from Process_Step_Location_Table where ProcessStep_ID = @temp_process_step_id and Location_ID = @temp_location_id
	end

	if @temp_cycle_time is null
	begin
		set @temp_cycle_time = 0
	end
	--print 'cycle time = '
	--print @temp_cycle_time
	select @temp_tt_normail_time = TT_Normal_Time from Transportation_Table where ProcessStep_ID =@temp_process_step_id and TT_Source_Location_ID =@temp_location_id and TT_Destination_Location_ID =@next_location_id 
	if @temp_tt_normail_time is null
	begin
		set @temp_tt_normail_time = 0
	end
	set @temp_ship_time = dateadd(day, @temp_tt_normail_time, @date_in)

	if @temp_ship_time is null
	begin
		set @temp_ship_time = 0
	end
	--print 'Ship Time ='
	--print @temp_ship_time
	set @temp_exp_date = dateadd(day, @temp_cycle_time, @temp_ship_time)
	insert into @fn_results(Expected_Date)values(@temp_exp_date)
	--select *from @fn_results
	--print 'Exp Date = '
	--print @temp_exp_date
	return
end




